package CSISL4pk;

import java.sql.*;
import java.util.*;

public class Case
{

    //member variables
    private int caseNum, invId, otherInvId, vid, sid, wid;
    private String type, area, date, notes, lastUpdateTime, status, victimName, suspectName, witnessName;
    //Need a temp address - John
    public Address location,  temp, temp2, temp3;
    public Person victim = new Person();
    public Person suspect = new Person();
    public Person witness = new Person();
    private ArrayList<Person> victimList = new ArrayList();
    private ArrayList<Person> suspectList = new ArrayList();
    private ArrayList<Person> witnessList = new ArrayList();
    private ResultSet rs;
    //no-argument constructor

    public Case()
    {
    }

    // case number methods
    public int getCaseNum()
    {
        return caseNum;
    }

    public void setCaseNum(int caseNum)
    {
        this.caseNum = caseNum;
    }

    // crime type methods
    public String getType()
    {
        return type;
    }

    public void setType(String type)
    {
        this.type = type;
    }

    // investigator ID methods
    public int getInvId()
    {
        return invId;
    }

    public void setInvId(int invId)
    {
        this.invId = invId;
    }

    // status methods
    public String getStatus()
    {
        return status;
    }

    public void setStatus(String status)
    {
        this.status = status;
    }

    // date methods
    public String getDate()
    {
        return date;
    }

    public void setDate(String date)
    {
        this.date = date;
    }

    // area methods
    public String getArea()
    {
        return area;
    }

    public void setArea(String area)
    {
        this.area = area;
    }

    // location methods
    public Address getLocation()
    {
        return location;
    }

    public void setLocation(Address location)
    {
        this.location = location;
    }

    // Victim Name
    public String getVictimName()
    {
        return victimName;
    }

    public void setVictimName(String victimName)
    {
        this.victimName = victimName;
    }

    // Suspect Name
    public String getSuspectName()
    {
        return suspectName;
    }

    public void setSuspectName(String suspectName)
    {
        this.suspectName = suspectName;
    }

    // Witness Name
    public String getWitnessName()
    {
        return witnessName;
    }

    public void setWitnessName(String witnessName)
    {
        this.witnessName = witnessName;
    }

    // victim methods
    public Person getVictim(int index)
    {
        return victimList.get(index);
    }

    public ArrayList<Person> getVictimList()
    {
        return victimList;
    }

    //victim methods
    public void addVictim()
    {
        victimList.add(new Person());
    }

    public void removeVictim(int index)
    {
        victimList.remove(index);
    }

    //witness methods
    public void addWitness()
    {
        witnessList.add(new Person());
    }

    public void removeWitness(int index)
    {
        witnessList.remove(index);
    }

    // suspect methods
    public Person getSuspect(int index)
    {
        return suspectList.get(index);
    }

    public ArrayList<Person> getSuspectList()
    {
        return suspectList;
    }

    public void addSuspect()
    {
        suspectList.add(new Person());
    }

    public void removeSuspect(int index)
    {
        suspectList.remove(index);
    }

    // notes methods
    public String getNotes()
    {
        return notes;
    }

    public void setNotes(String notes)
    {
        this.notes = notes;
    }

    // update time methods
    public String getLastUpdateTime()
    {
        return lastUpdateTime;
    }

    public void setLastUpdateTime(String lastUpdateTime)
    {
        this.lastUpdateTime = lastUpdateTime;
    }

    @Override
    public String toString()
    {
        String temp = "";
        //Added a try catch for null pointer exception
        try
        {
            temp += "Case Number: " + caseNum
                    + "\nCrime Type:\t" + type
                    + "\nDate:\t" + date
                    + "\nArea:\t" + area
                    + "\nLocation:\t" + location.toString();
            for (int c = 0; c < victimNumber(); c++)
            {
                temp += "\nVictim:\t" + victimList.get(c).toString();
            }
            for (int c = 0; c < suspectNumber(); c++)
            {
                temp += "\nVictim:\t" + suspectList.get(c).toString();
            }
        } catch (NullPointerException e)
        {
        }
        return temp;
    }

    private int suspectNumber()
    {
        return suspectList.size();
    }

    private int victimNumber()
    {
        return victimList.size();
    }

    public void loadCase(int caseNum)
    {

        try
        {
            //Temp String to grab addresses - John
            String id = "";

            System.out.println(caseNum);

            String myDriver = "com.mysql.jdbc.Driver";

            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";

            Class.forName(myDriver);

            //Connection conn = DriverManager.getConnection(myUrl, "pablofig","cs2450");
            Connection conn = DriverManager.getConnection(myUrl, "csisaltlake", "cs2450");
            
            // ******NEED TO GET CASE ADDRESS INFO ******************************
            String caseAddr = "SELECT * FROM addresses WHERE person_id = 0 AND case_id ="+caseNum;
            System.out.printf(caseAddr);
            String caseInfo = "SELECT * FROM cases c LEFT JOIN users ON users.id = c.inv_ID WHERE c.caseNum = " + caseNum;

            // create the java statement
            Statement st = conn.createStatement();
            rs = st.executeQuery(caseAddr);
            while(rs.next())
            {
                location = new Address();
                location.setStreet(rs.getString("address"));
                location.setCity(rs.getString("city"));
                location.setState(rs.getString("state"));
                location.setZip(rs.getInt("zip"));
            }
            // execute the query
            rs = st.executeQuery(caseInfo);
            int i = 1;
            while (rs.next() && i <= 3)
            {
                this.status = rs.getString("status");
                this.type = rs.getString("crimeType");
                this.area = rs.getString("locale");
                this.date = rs.getString("crimedate");
                this.notes = rs.getString("notes");
                this.lastUpdateTime = rs.getString("lastUpdateTime");
                this.invId = rs.getInt("inv_ID");

            }
            System.out.println(this.date);

            //Victim Info
//            String victimInfo = "SELECT * FROM person p, case_to_person cp "
//                    + "WHERE cp.case_id = " + caseNum + " AND p.id = cp.person_id AND type = 'victim' ";

            String victimInfo = "SELECT * FROM person p, case_to_person cp LEFT JOIN addresses a ON a.person_id = cp.person_id "
                    + "WHERE cp.case_id = " + caseNum + " AND p.id = cp.person_id AND type = 'victim'";

            // execute the query
            rs = st.executeQuery(victimInfo);
            int t = 1;
            while (rs.next() && t<2)
            {
                temp = new Address();
                //this is how I save the person info into the case object.
                this.victim.setFirst(rs.getString("first"));
                this.victim.setLast(rs.getString("last"));
                this.victim.setMiddle(rs.getString("middle"));
                this.victim.setDob(rs.getString("dob"));
                this.victim.setSSN(rs.getString("ssn"));
                this.victim.setPhone(rs.getString("phone"));
                this.victim.setGender(rs.getString("gender").charAt(0));
                temp.setApt(rs.getString("apt"));
                temp.setBusiness(rs.getBoolean("is_business"));
                temp.setCity(rs.getString("city"));
                temp.setState(rs.getString("state"));
                temp.setStreet(rs.getString("address"));
                temp.setZip(rs.getInt("zip"));
                this.victim.setAddress(temp);
                t++;
            }

            //Suspect Info
            String suspectInfo = "SELECT * FROM person p, case_to_person cp LEFT JOIN addresses a ON a.person_id = cp.person_id "
                    + "WHERE cp.case_id = " + caseNum + " AND p.id = cp.person_id AND type = 'suspect'";//"SELECT * FROM person p, case_to_person cp "
                   // + "WHERE cp.case_id = " + caseNum + " AND p.id = cp.person_id AND type = 'suspect' ";
           
            // execute the query
            rs = st.executeQuery(suspectInfo);
            int z = 1;
            while (rs.next() && z==1)
            {
                temp2 = new Address();
                this.suspect.setFirst(rs.getString("first"));
                this.suspect.setLast(rs.getString("last"));
                this.suspect.setMiddle(rs.getString("middle"));
                this.suspect.setDob(rs.getString("dob"));
                this.suspect.setSSN(rs.getString("ssn"));
                this.suspect.setPhone(rs.getString("phone"));
                this.suspect.setGender(rs.getString("gender").charAt(0));
                temp2.setApt(rs.getString("apt"));
                temp2.setBusiness(rs.getBoolean("is_business"));
                temp2.setCity(rs.getString("city"));
                temp2.setState(rs.getString("state"));
                temp2.setStreet(rs.getString("address"));
                temp2.setZip(rs.getInt("zip"));
                this.suspect.setAddress(temp2);
                z = 2;
            }

            //Witness Info
            String witnessInfo = "SELECT * FROM person p, case_to_person cp LEFT JOIN addresses a ON a.person_id = cp.person_id "
                    + "WHERE cp.case_id = " + caseNum + " AND p.id = cp.person_id AND type = 'witness'";//"SELECT * FROM person p, case_to_person cp "
                   // + "WHERE cp.case_id = " + caseNum + " AND p.id = cp.person_id AND type = 'witness' ";
            
            // execute the query
            rs = st.executeQuery(witnessInfo);
            while (rs.next())
            {
                temp3 = new Address();
                this.witness.setFirst(rs.getString("first"));
                this.witness.setLast(rs.getString("last"));
                this.witness.setMiddle(rs.getString("middle"));
                this.witness.setDob(rs.getString("dob"));
                this.witness.setSSN(rs.getString("ssn"));
                this.witness.setPhone(rs.getString("phone"));
                this.witness.setGender(rs.getString("gender").charAt(0));
                temp3.setApt(rs.getString("apt"));
                temp3.setBusiness(rs.getBoolean("is_business"));
                temp3.setCity(rs.getString("city"));
                temp3.setState(rs.getString("state"));
                temp3.setStreet(rs.getString("address"));
                temp3.setZip(rs.getInt("zip"));
                this.witness.setAddress(temp3);
            }


            System.out.println("loading cases...");

            //on finish always close connection
            st.close();

        } catch (Exception e)
        {
            System.out.println(e);
        }

    }


    public void submitNewCase()

    {
        
        try
        { // this submits the case info to the DB

            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";
            Class.forName(myDriver);
            Connection conn = DriverManager.getConnection(myUrl, "csisaltlake", "cs2450");
            String nCQuery = "UPDATE cases SET status = 'open',crimeType = '" + this.getType() + "',"
                    + "locale = '" + this.getArea() + "', crimeDate = '" + this.getDate() + "',"
                    + " notes = '" + this.getNotes() + "',lastUpdateTime = NOW(), inv_ID =" + this.getInvId()
                    + " WHERE caseNum = " + this.getCaseNum() + " ";

            // for new cases insert
            String query2 = "INSERT INTO person(user_id, type,first,middle,last,dob,ssn,gender,caseNum) "//,dob,ssn,gender
                    + "VALUES(" + this.getInvId() + ",'victim', '" + this.victim.getFirst() + "', '"
                    + this.victim.getMiddle() + "','" + this.victim.getLast() + "','" + this.victim.getDob() + "', '"
                    + this.victim.getSSN() + "','"
                    + this.victim.getGender() + "',"
                    + this.getCaseNum() + "),(" + this.getInvId() + ",'suspect'"
                    + ", '" + this.suspect.getFirst() + "', '" + this.suspect.getMiddle() + "','" + this.suspect.getLast() + "','"
                    + this.suspect.getDob() + "', '"
                    + this.suspect.getSSN() + "', "
                    + "'" + this.suspect.getGender() + "',"
                    + this.getCaseNum() + "),(" + this.getInvId() + ",'witness'"
                    + ", '" + this.witness.getFirst() + "', '" + this.witness.getMiddle() + "','" + this.witness.getLast() + "','"
                    + this.witness.getDob() + "', '"
                    + this.witness.getSSN() + "', "
                    + "'" + this.witness.getGender() + "',"
                    + this.getCaseNum() + ")";

            // create the java statement
            Statement st = conn.createStatement();
            Statement st2 = conn.createStatement();
            Statement st3 = conn.createStatement();
            Statement st4 = conn.createStatement();
            Statement st5 = conn.createStatement();

            //get personid
            String query3 = "SELECT id from person where user_id = " + this.getInvId() + " and "
                    + "type = 'victim' and caseNum =" + this.getCaseNum() + " or user_id = " + this.getInvId() + " and "
                    + "type = 'suspect' and caseNum =" + this.getCaseNum() + " or user_id = " + this.getInvId() + " and "
                    + "type = 'witness' and caseNum =" + this.getCaseNum();

            System.out.println(nCQuery);
            
            // execute the query
            st.execute(nCQuery);
            st.close();
            System.out.println(nCQuery);
            st2.execute(query2);
            st2.close();
            System.out.println(query2);
            rs = st3.executeQuery(query3);//get person id
            int i = 1;
            while (rs.next())
            {
                if (i == 1)
                {
                    vid = rs.getInt("id");
                    i++;
                    System.out.println(vid + " : " + i);
                } else if (i == 2)
                {
                    sid = rs.getInt("id");
                    i++;
                    System.out.println(sid + " : " + i);
                } else if (i == 3)
                {
                    wid = rs.getInt("id");
                    i++;
                    System.out.println(wid + " : " + i);
                }
            }

            //on finish always close connection 
            st3.close();
            //for new cases insert
            System.out.println("q4:");
            //if NEW
            String query4 = "INSERT INTO addresses (address,apt,city,state,zip,person_id,case_id) VALUES"
                    + "('" + this.victim.address.getStreet() + "','" + this.victim.address.getApt() + "',"
                    + "'" + this.victim.getAddress().getCity() + "','" + this.victim.getAddress().getState() + "',"
                    + "'" + this.victim.getAddress().getZip() + "'," + this.vid + "," + this.getCaseNum() + "),"
                    + "('" + this.location.getStreet() + "','empty',"
                    + "'" + this.location.getCity() + "','" + this.location.getState() + "',"
                    + "'" + this.location.getZip() + "',0," + this.getCaseNum() + "),"
                    + "('" + this.suspect.address.getStreet() + "','" + this.suspect.address.getApt() + "',"
                    + "'" + this.suspect.getAddress().getCity() + "','" + this.suspect.getAddress().getState() + "',"
                    + "'" + this.suspect.getAddress().getZip() + "'," + this.sid + "," + this.getCaseNum() + "),"
                    + "('" + this.witness.address.getStreet() + "','" + this.witness.address.getApt() + "',"
                    + "'" + this.witness.getAddress().getCity() + "','" + this.witness.getAddress().getState() + "',"
                    + "'" + this.witness.getAddress().getZip() + "'," + this.wid + "," + this.getCaseNum() + ")";
            st4.execute(query4);
            st4.close();
            System.out.println(query4);
            // for new cases insert
            String query5 = "INSERT INTO case_to_person(case_id, person_id, personType) VALUES(" + this.getCaseNum() + ","
                    + this.vid + ",'victim'),"
                    + "(" + this.getCaseNum() + "," + this.sid + ",'suspect'),(" + this.getCaseNum() + ","
                    + this.wid + ",'witness')";
            st5.execute(query5);
            st5.close();
        } catch (Exception e)
        {
            System.out.println(e);

        }
    }

    
    public void submitEditCase()
    {
        try
        { // this submits the case info to the DB

            String myDriver = "com.mysql.jdbc.Driver";
            String myUrl = "jdbc:mysql://www.db4free.net:3306/csisl";//www.db4free.net:3306/csisl";//www.freesql.org:3306/csisl";//"jdbc:mysql://SQL09.FREEMYSQL.NET:3306/csisl";
            Class.forName(myDriver);
            Connection conn = DriverManager.getConnection(myUrl, "csisaltlake", "cs2450");
            Statement st = conn.createStatement();
            // updates case info to cases table
            String nCQuery = "UPDATE cases SET status = 'open',crimeType = '" + this.getType() + "', "
                    + "locale = '" + this.getArea() + "', crimeDate = '" + this.getDate() + "', "
                    + " notes = '" + this.getNotes() + "',lastUpdateTime = NOW(), inv_ID =" + this.getInvId()
                    + " WHERE caseNum = " + this.getCaseNum() + " ";
            
            // update person table with values for victim suspect and witness
            String query2 = "update person set user_id = " + this.getInvId() + ", first = '" + this.victim.getFirst() + "'"
                        + ",middle = '"+ this.victim.getMiddle() + "',last = '"+ this.victim.getLast() + "' "
                        + ",dob = '" + this.victim.getDob() + "',ssn = "+ this.victim.getSSN() + " "
                        + ", gender = '"+ this.victim.getGender() + "' where type = 'victim' and id = "+ vid;
                
                String susQ = "update person set user_id = " + this.getInvId() + ", first = '" + this.suspect.getFirst() + "'"
                        + ",middle = '"+ this.suspect.getMiddle() + "',last = '"+ this.suspect.getLast() + "' "
                        + ", dob = '" + this.suspect.getDob() + "',ssn = "+ this.suspect.getSSN() + ""
                        + ", gender = '"+ this.suspect.getGender() + "' where type = 'suspect' and id = "+ sid;
                
                String witQ = "update person set user_id = " + this.getInvId() + ", first = '" + this.witness.getFirst() + "'"
                        + ",middle = '"+ this.witness.getMiddle() + "',last = '"+ this.witness.getLast() + "'"
                        + ",dob = '" + this.witness.getDob() + "',ssn = "+ this.witness.getSSN() + ""
                        + ",gender = '"+ this.witness.getGender() + "' where type = 'witness' and id = "+ wid;
                st.execute(query2);
                st.execute(susQ);
                st.execute(witQ);
                st.execute(nCQuery);
            //get personid
            String query3 = "SELECT id from person where user_id = " + this.getInvId() + " and "
                    + "type = 'victim' and caseNum =" + this.getCaseNum() + " or user_id = " + this.getInvId() + " and "
                    + "type = 'suspect' and caseNum =" + this.getCaseNum() + " or user_id = " + this.getInvId() + " and "
                    + "type = 'witness' and caseNum =" + this.getCaseNum();
            
            rs = st.executeQuery(query3);//get person id
            int i = 1;//cycle through rows
            while (rs.next())
            {
                if (i == 1)
                {
                    vid = rs.getInt("id");
                    i++;
                    System.out.println(vid + " : " + i);
                } else if (i == 2)
                {
                    sid = rs.getInt("id");
                    i++;
                    System.out.println(sid + " : " + i);
                } else if (i == 3)
                {
                    wid = rs.getInt("id");
                    i++;
                    System.out.println(wid + " : " + i);
                }
            }

            
            
//            System.out.println("q4:");
            String query4 = "UPDATE addresses SET address = '" + this.victim.address.getStreet() + "',"
                    + "apt = '" + this.victim.address.getApt() + "',city = '" + this.victim.getAddress().getCity() + "',"
                    + "state = '" + this.victim.getAddress().getState() + "',zip =" + this.victim.getAddress().getZip() + ""
                    + " WHERE person_id= "+ this.vid + " AND case_id="+ this.getCaseNum();
            String q4s = "UPDATE addresses SET address = '" + this.suspect.address.getStreet() + "',"
                    + "apt = '" + this.suspect.address.getApt() + "',city = '" + this.suspect.getAddress().getCity() + "',"
                    + "state = '" + this.suspect.getAddress().getState() + "',zip =" + this.suspect.getAddress().getZip() + ""
                    + " WHERE person_id= "+ this.sid + " AND case_id="+ this.getCaseNum();
            String q4w = "UPDATE addresses SET address = '" + this.witness.address.getStreet() + "',"
                    + "apt = '" + this.witness.address.getApt() + "',city = '" + this.witness.getAddress().getCity() + "',"
                    + "state = '" + this.witness.getAddress().getState() + "',zip =" + this.witness.getAddress().getZip() + ""
                    + " WHERE person_id= "+ this.wid + " AND case_id="+ this.getCaseNum();
            String q4l = "UPDATE addresses SET address = '" + this.location.getStreet() + "',"
                    + "city = '" + this.location.getCity() + "',"
                    + "state = '" + this.location.getState() + "',zip =" + this.location.getZip() + ""
                    + " WHERE person_id = 0 AND case_id="+ this.getCaseNum();
            st.execute(query4);
            st.execute(q4s);
            st.execute(q4w);
            st.execute(q4l);
            
//            System.out.println(query4);
            //always close connection
            st.close();
        } catch (Exception e)
        {
            System.out.println(e);

        }
    }
    
    public static void main(String[] args)
    {
        Case t = new Case();
        t.setArea("Avenues");
        t.setCaseNum(100000);
        t.setDate("10-11-11");
        t.setStatus("open");
        System.out.println(t);
    }
}